﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class StaffActiveDAL
    {
        private const string SQL_SELECT_LAST_LOGIN = "select SFA_STAFF_ID,SFA_LOGIN_TIME,SFA_LOGOUT_TIME,SFA_COMPUTER_NAME,SFA_IP_ADDRESS,SFA_MAC_ADDRESS,SFA_SESSION_ID from (select * from WS_STAFF_ACTIVITIES where SFA_STAFF_ID=@StaffId and SFA_LOGOUT_TIME is NULL order by SFA_LOGIN_TIME desc) as A where limit 1";
        private const string SQL_INSERT_WS_STAFF_ACTIVITIES = "insert into WS_STAFF_ACTIVITIES(SFA_STAFF_ID,SFA_LOGIN_TIME,SFA_COMPUTER_NAME,SFA_IP_ADDRESS,SFA_MAC_ADDRESS,SFA_SESSION_ID) values(@StaffId,@LoginTime,@ComputerName,@IpAddress,@MacAddress,@SessionId)";
        private const string SQL_UPDATE_LAST_LOGOUT_TIME = "update WS_STAFF_ACTIVITIES set SFA_LOGOUT_TIME=@LogoutTime where 1=1 ";

        private const string SQL_WHERE_STAFF_ID = " and SFA_STAFF_ID=@StaffId ";
        private const string SQL_WHERE_USER_ACTIVITY_WITH_COMPUTER_AND_LOGIN = " and SFA_STAFF_ID=@StaffId and SFA_COMPUTER_NAME=@ComputerName and SFA_LOGOUT_TIME is NULL ";
        private const string SQL_WHERE_USER_ACTIVITY_WITH_USER_ID = " and SFA_STAFF_ID=@StaffId and UAC_LOGOUT_DATE_TIME is NULL ";

        private const string PARM_STAFF_ID = "@StaffId";
        private const string PARM_LOGIN_TIME = "@LoginTime";
        private const string PARM_LOGOUT_TIME = "@LogoutTime";
        private const string PARM_COMPUTER_NAME = "@ComputerName";
        private const string PARM_IP_ADDRESS = "@IpAddress";
        private const string PARM_MAC_ADDRESS = "@MacAddress";
        private const string PARM_SESSIOIN_ID = "@SessionId";

        private static StaffActiveDAL instance = new StaffActiveDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static StaffActiveDAL Instance
        {
            get
            {
                return instance;
            }
        }

        public StaffActiveModel GetLastLogin(string id)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_LAST_LOGIN);
            sbSQL.Append(SQL_WHERE_STAFF_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new StaffActiveModel()
                    {
                        StaffId = Convert.ToString(t.ItemArray[0]),
                        LoginTime = Convert.ToDateTime(t.ItemArray[1]),
                        LogoutTime = Convert.ToDateTime(t.ItemArray[2]),
                        ComputerName = Convert.ToString(t.ItemArray[3]),
                        IPAddress = Convert.ToString(t.ItemArray[4]),
                        MACAddress = Convert.ToString(t.ItemArray[5])
                    };
                }
                return null;
            }
        }
        public int Insert(DbTransaction trans, StaffActiveModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.StaffId });
            parms.Add(new MySqlParameter(PARM_LOGIN_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.LoginTime });
            parms.Add(new MySqlParameter(PARM_COMPUTER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ComputerName });
            parms.Add(new MySqlParameter(PARM_IP_ADDRESS, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.IPAddress });
            parms.Add(new MySqlParameter(PARM_MAC_ADDRESS, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.MACAddress });
            parms.Add(new MySqlParameter(PARM_SESSIOIN_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.SessionId });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_STAFF_ACTIVITIES, parms.ToArray());
        }
        /// <summary>
        /// 设置用户在某计算机中最后一次退出系统的时间
        /// </summary>
        /// <param name="trans">连接字符串</param>
        /// <param name="id">用户Id</param>
        /// <param name="computerName">计算机名</param>
        /// <param name="logoutDateTime">退出系统的时间</param>
        /// <returns>返回受影响的记录数</returns>
        public int SetLastLogoutTime(DbTransaction trans, string id, string computerName, DateTime logoutDateTime)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_LAST_LOGOUT_TIME);
            sbSQL.Append(SQL_WHERE_USER_ACTIVITY_WITH_COMPUTER_AND_LOGIN);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });
            parms.Add(new MySqlParameter(PARM_COMPUTER_NAME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = computerName });
            parms.Add(new MySqlParameter(PARM_LOGOUT_TIME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = logoutDateTime });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        /// <summary>
        /// 设置用户在某计算机中最后一次退出系统的时间
        /// </summary>
        /// <param name="trans">连接字符串</param>
        /// <param name="id">用户Id</param>
        /// <param name="computerName">计算机名</param>
        /// <param name="logoutDateTime">退出系统的时间</param>
        /// <returns>返回受影响的记录数</returns>
        public int SetLastLogoutTime(DbTransaction trans, string id, DateTime logoutDateTime)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_LAST_LOGOUT_TIME);
            sbSQL.Append(SQL_WHERE_USER_ACTIVITY_WITH_COMPUTER_AND_LOGIN);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = id });
            parms.Add(new MySqlParameter(PARM_LOGOUT_TIME, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = logoutDateTime });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
    }
}
